package org.pan.poi.poi;

import java.io.File;
import java.io.FileOutputStream;

import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author panmingshuai
 * @description 
 * @Time 2018年4月2日  下午4:29:08
 *
 */
public class ExcelTest {
	@SuppressWarnings("resource")
	public static void main(String[] args) throws Exception {
		/**
		 * 读取excel
		 */
//		FileInputStream in = new FileInputStream(new File("E:\\mz.xlsx"));
		//读取xlsx的excel
//		XSSFWorkbook workbook = new XSSFWorkbook(in);
		//读取xls的excel
//		HSSFWorkbook workbook = new HSSFWorkbook(in);
		
		//循环遍历excel表
//		for(int i=0; i<workbook.getNumberOfSheets(); i++){
//			XSSFSheet sheet = workbook.getSheetAt(i);
//			for(int j=0; j<=sheet.getLastRowNum(); j++){
//				XSSFRow row = sheet.getRow(j);
//				for(int k=0; k<row.getLastCellNum(); k++){
//					XSSFCell cell = row.getCell(k);
//					String dm = cell.toString();
//					if(k == row.getLastCellNum()-1){
//						System.out.println(dm);
//					} else {
//						System.out.print(dm);
//					}
//				}
//			}
//		}
		
		//数值转换的方法
//		XSSFSheet sheet = workbook.getSheetAt(0);
//		XSSFRow row = sheet.getRow(0);
//		XSSFCell cell0 = row.getCell(0);
//		XSSFCell cell1 = row.getCell(1);
//		XSSFCell cell2 = row.getCell(2);
//		System.out.println(Double.valueOf(cell0.toString()) + 10);//浮点数
//		System.out.println(Math.round(Float.valueOf(cell1.toString()))+10);//整数
//		System.out.println(Math.round(Double.valueOf(cell2.toString()))+10);//长整型
		
		
		
		
		/**
		 * 写入excel
		 */
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet();
		XSSFRow row0 = sheet.createRow(0);
		XSSFCell cell0 = row0.createCell(0);
		cell0.setCellValue("pan");
		XSSFCell cell1 = row0.createCell(1);
		cell1.setCellValue(123);
		XSSFCell cell2 = row0.createCell(2);
		cell2.setCellValue(123.2352545);
		
		//单元格合并和样式设置
		XSSFRow row1 = sheet.createRow(1);
		XSSFCell cell10 = row1.createCell(0);
		XSSFCell cell11 = row1.createCell(1);
		cell10.setCellValue("cell10");
		cell11.setCellValue("cell11");
		
		//为cell10设置样式
		XSSFCellStyle cellStyle = workbook.createCellStyle();//单元格样式
		Font fontStyle = workbook.createFont(); // 字体样式 
		fontStyle.setBold(true); // 加粗  
        fontStyle.setFontName("黑体"); // 字体  
        fontStyle.setFontHeightInPoints((short) 11); // 大小
        // 将字体样式添加到单元格样式中   
        cellStyle.setFont(fontStyle);  
        // 居中  
        cellStyle.setAlignment(HorizontalAlignment.CENTER);  
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);  
        cellStyle.setBorderLeft(BorderStyle.THIN);  
        cellStyle.setBorderRight(BorderStyle.THIN);  
        cellStyle.setBorderTop(BorderStyle.THIN);  
        // 为cell10单元格添加样式
        cell10.setCellStyle(cellStyle); 
        
        // 合并单元格  
        CellRangeAddress cra =new CellRangeAddress(1, 3, 1, 3); // 起始行, 终止行, 起始列, 终止列  
        sheet.addMergedRegion(cra);  
        //为cell11设置样式，因为这时已经合并，所以现在cell11的样式就是合并单元格的样式
      	XSSFCellStyle cellStyle1 = workbook.createCellStyle();//单元格样式
      	//水平居中
      	cellStyle1.setAlignment(HorizontalAlignment.CENTER);
      	//垂直居中
      	cellStyle1.setVerticalAlignment(VerticalAlignment.TOP);
      	cell11.setCellStyle(cellStyle1);
          
        // 使用RegionUtil类为合并后的单元格添加边框 ，如果不设置就只有左上角的那个合并前的单元格有上和左的边框线
        RegionUtil.setBorderBottom(1, cra, sheet); // 下边框  
        RegionUtil.setBorderLeft(1, cra, sheet); // 左边框  
        RegionUtil.setBorderRight(1, cra, sheet); // 有边框  
        RegionUtil.setBorderTop(1, cra, sheet); // 上边框 
        
        
		
		
		
		File file = new File("E://pan.xlsx");
		FileOutputStream out = FileUtils.openOutputStream(file);
		workbook.write(out);
		out.close();
	}
}
